<?php
/*
 * @Author: CYH
 * @Date: 2020-10-31 10:25:06
 * @LastEditTime: 2021-05-12 11:28:02
 * @LastEditors: Please set LastEditors
 * @Description: 入住\退宿\调宿管理control类
 * @FilePath: \application\controllers\Register\Register.php
 */
    // 指定允许其他域名访问  
    header('Access-Control-Allow-Origin:*');
    // 响应类型  
    header('Access-Control-Allow-Methods:POST');
    // 响应头设置  
    header('Access-Control-Allow-Headers:x-requested-with,content-type:application/json,charset:utf-8');
    require_once 'PHPExcel-1.8\Classes\PHPExcel.php';
    require_once 'PHPExcel-1.8\Classes\PHPExcel\IOFactory.php';
    // require_once 'PHPExcel\Classes\PHPExcel\Reader\Excel5.php';
    require_once 'PHPExcel-1.8\Classes\PHPExcel\Reader\Excel2007.php';
    require_once 'application\controllers\Cost\ApplicationForm.php';

    // 入住\退宿\调宿管理control类
    class Register extends CI_Controller
    {
        // 构造方法
        public function __construct()
        {

            parent::__construct();
            $this->load->model('Register/Check_in');//入住
            $this->load->model('Register/Retreat');//退宿
            $this->load->model('Register/Register_manage');//入住/退宿/调宿 记录查询
            $this->load->model('Register/Tune_in');//调宿
            $this->load->model('Cost/ApplicationForm_model');
            $this->load->helper('url_helper');
            $this->load->library('session');
            // $this->load->helper('date');
            $this->load->helper(array('form', 'url', 'date'));
        }

        //调用U8人员数据
        // public function erp_connect()
        // {
        //     $RY_name = $_POST['RY_name']; //人员姓名
        //     // $RY_name = ""; //人员姓名

        //     $result_json = $this->Check_in->erp_connect($RY_name);

        //     echo $result_json;

        //     return 0;
        // }

        /**
         * @description: 入住申请人员查询
         * @param {name, idNumber, limit}
         * @return json:array('res'=>'','msg'=>'','data'=>'')
         */        
        public function check_in_people_select()
        {

            $name = $_POST['name'];                     //姓名
            $idNumber = $_POST['idNumber'];             //身份证号
            $limit = $_POST['limit'];                   //页数

            // $name = "";
            // $idNumber = "";
            // $limit = "";

            $result_json = $this->Check_in->check_in_people_select(
                $name,
                $idNumber,
                $limit
            );

            echo $result_json;

            return 0;
        }

        /**
         * @description: 入住方法：申请人节点
         * @param {name, sex, idNumber, address, phone, emergencyContact, relationShip, emergencyPhone, department, job, jobNumber, temporary}
         * @return json:array('res'=>'','msg'=>'','data'=>'')
         */        
        public function check_in_applicant()
        {

            $name = $_POST['name'];                             //姓名
            $sex = $_POST['sex'];                               //性别
            $idNumber = $_POST['idNumber'];                     //身份证号
            $address = $_POST['address'];                       //户籍地址
            $phone = $_POST['phone'];                           //联系方式
            $emergencyContact = $_POST['emergencyContact'];     //紧急联系人
            $relationShip = $_POST['relationShip'];             //联系人关系
            $emergencyPhone = $_POST['emergencyPhone'];         //紧急联系人联系方式
            $department = $_POST['department'];                 //部门
            $job = $_POST['job'];                               //岗位
            $jobNumber = $_POST['jobNumber'];                   //工号
            $temporary = $_POST['temporary'];                   //临时住宿标示

            // $name = "张五"; 
            // $sex = "1"; 
            // $idNumber = "620102200007193934"; 
            // $address = "浙江省宁波市慈溪市";
            // $phone = "13893691560"; 
            // $emergencyContact = "张三";
            // $relationShip = "哥哥"; 
            // $emergencyPhone = "13893691561";
            // $department = "IT部";
            // $job = "开发工程师";
            // $jobNumber = "";
            // $temporary = "";

            $result_json = $this->Check_in->check_in_applicant(
                $name, 
                $sex, 
                $idNumber,
                $address,
                $phone,
                $emergencyContact,
                $relationShip,
                $emergencyPhone,
                $department,
                $job,
                $jobNumber,
                $temporary
            );

            echo $result_json;

            return 0;
        }

        //入住方法：人资节点
        // public function check_in_hr(){

        //     $requestid = $_POST['requestid'];           //请求id
        //     $department = $_POST['department'];         //部门
        //     $job = $_POST['job'];                       //岗位
        //     $username = $_POST['username'];             //用户名

        //     $result_json = $this->Check_in->check_in_hr(
        //         $requestid,
        //         $department,
        //         $job,
        //         $username
        //     );

        //     echo $result_json;

        //     return 0;
        // }

        /**
         * @description: 入住方法:宿管员节点
         * @param {name, idNumber, liveDate, area_number, floor_number, dorm_floor, room_number, bedNumber, remarks}
         * @return json:array('res'=>'','msg'=>'','data'=>'')
         */        
        public function check_in_admin()
        {

            $name = $_POST['name'];                     //姓名
            $idNumber = $_POST['idNumber'];             //身份证号
            $jobNumber = $_POST['jobNumber'];           //工号
            $job = $_POST['job'];                       //岗位
            $liveDate = $_POST['liveDate'];             //入住时间
            $area_number = $_POST['area_number'];       //区域编号
            $floor_number = $_POST['floor_number'];     //宿舍楼编号
            $dorm_floor = $_POST['dorm_floor'];         //宿舍楼层号
            $room_number = $_POST['room_number'];       //宿舍房间号
            $bedNumber = $_POST['bedNumber'];           //床位号
            $sex = $_POST['sex'];                       //性别
            $location = $_POST['location'];             //户籍地址
            $phone = $_POST['phone'];                   //联系方式
            $department_2 = $_POST['department_2'];     //二级部门
            $department_3 = $_POST['department_3'];     //三级部门
            $company = $_POST['company'];               //是否公司员工 1.非本公司 0.本公司
            $remarks = $_POST['remarks'];               //备注
            
            // $name = "杨少天";
            // $idNumber = "123456789";
            // $jobNumber = "11166";        
            // $job = "软件开发工程师";                     
            // $liveDate = "2021-01-08";
            // $area_number = "1585";
            // $floor_number = "1";
            // $dorm_floor = "5";
            // $room_number = "515";
            // $bedNumber = "4";
            // $sex = "0";
            // $location = "慈溪";
            // $phone = "15797621896";
            // $department_2 = "集团信息管控中心";
            // $department_3 = "软件开发组";
            // $company = "0";
            // $remarks = "测试";
            
            $result_json = $this->Check_in->check_in_admin(
                $name,
                $idNumber,
                $jobNumber,
                $job,
                $liveDate,
                $area_number,
                $floor_number,
                $dorm_floor,
                $room_number,
                $bedNumber,
                $sex,
                $location,
                $phone,
                $department_2,
                $department_3,
                $company,
                $remarks
            );

            echo $result_json;

            return 0;
        }

        /**
         * @description: 入住筛选界面方法:取宿舍楼
         * @param {area_number}
         * @return json:array('res'=>'','msg'=>'','data'=>'')
         */        
        public function check_condition()
        {

            $area_number = $_POST['area_number']; //区域名称

            // $area_number = "1585";

            $result_json = $this->Check_in->check_condition($area_number);

            echo $result_json;

            return 0;
        }

        /**
         * @description: 入住筛选界面方法:取楼层
         * @param {area_number, floor_number}
         * @return json:array('res'=>'','msg'=>'','data'=>'')
         */        
        public function check_condition_floor()
        {

            $area_number = $_POST['area_number'];     //区域名称
            $floor_number = $_POST['floor_number'];   //宿舍楼名称

            // $area_number = "1585";
            // $floor_number = "1";

            $result_json = $this->Check_in->check_condition_floor(
                $area_number,
                $floor_number
            );

            echo $result_json;

            return 0;
        }

        /**
         * @description: 入住筛选界面方法:取宿舍
         * @param {area_number, floor_number, dorm_floor, department, sex}
         * @return json:array('res'=>'','msg'=>'','data'=>'')
         */        
        // public function check_condition_number()
        // {
            
        //     $area_number = $_POST['area_number'];    //区域编号
        //     $floor_number = $_POST['floor_number'];  //宿舍楼编号
        //     $dorm_floor = $_POST['dorm_floor'];      //宿舍楼层号
        //     $department = $_POST['department'];      //部门
        //     $company = $_POST['company'];            //是否公司员工 1.非本公司 0.本公司
        //     $sex = $_POST['sex'];                    //性别

        //     // $area_number = "1585";
        //     // $floor_number = "1";
        //     // $dorm_floor = "4";
        //     // $department = "";
        //     // $company = "1";
        //     // $sex = "0";
            
        //     $result_json = $this->Check_in->check_condition_number(
        //         $area_number,
        //         $floor_number,
        //         $dorm_floor,
        //         $department,
        //         $company,
        //         $sex
        //     );

        //     echo $result_json;

        //     return 0;
        // }

        /**
         * @description: 入住筛选界面方法:取宿舍
         * @param {area_number, floor_number, dorm_floor, department,sex}
         * @return json:array('res'=>'','msg'=>'','data'=>'')
         */
        public function check_condition_number()
        {
            
            $area_number = $_POST['area_number'];    //区域编号
            $floor_number = $_POST['floor_number'];  //宿舍楼编号
            $dorm_floor = $_POST['dorm_floor'];      //宿舍楼层号
            $department_2 = $_POST['department_2'];  //二级部门
            $department_3 = $_POST['department_3'];  //三级部门
            $sex = $_POST['sex'];                    //性别
            $room_type = $_POST['room_type'];        //房间类型
            $live_empty = $_POST['live_empty'];      //是否入住空房间
            $limit = $_POST['page'];                 //页数
            
            // $area_number = "";
            // $floor_number = "";
            // $dorm_floor = "";
            // $department_2 = "";
            // $department_3 = "";
            // $sex = "";
            // $room_type = "2";
            // $live_empty = "0";
            // $limit = "1";

            $result_json = $this->Check_in->check_condition_number(
                $area_number,
                $floor_number,
                $dorm_floor,
                $department_2,
                $department_3,
                $sex,
                $room_type,
                $live_empty,
                $limit
            );

            echo $result_json;

            return 0;
        }

        // //入住推荐
        // public function check_condition_number1()
        // {

        //     $sex = $_POST['sex'];                   //性别
        //     $department = $_POST['department'];     //部门
        //     // $company = $_POST['company'];        //是否公司员工 1.非本公司 0.本公司               

        //     // $sex = "0";
        //     // $department = "装配一部";
        //     // $company = '0';

        //     $result_json = $this->Check_in->check_condition_number1(
        //         $sex,
        //         $department
        //         // $company
        //     );

        //     echo $result_json;

        //     return 0;
        // }


        /**
         * @description: 入住筛选界面方法:取床位与柜子
         * @param {area_number, floor_number, dorm_floor, room_number}
         * @return json:array('res'=>'','msg'=>'','data'=>'')
         */        
        public function check_condition_bed_table()
        {

            $area_number = $_POST['area_number'];       //区域编号
            $floor_number = $_POST['floor_number'];     //宿舍楼编号
            $dorm_floor = $_POST['dorm_floor'];         //宿舍楼层号
            $room_number = $_POST['room_number'];       //宿舍房间号

            // $area_number = "333";
            // $floor_number = "1";
            // $dorm_floor = "1";
            // $room_number = "105";

            $result_json = $this->Check_in->check_condition_bed_table(
                $area_number,
                $floor_number,
                $dorm_floor,
                $room_number
            );

            echo $result_json;

            return 0;
        }

        /**
         * @description: 入住人员查询方法
         * @param {date_start, date_end,type, name, jobNumber, limit}
         * @return json:array('res'=>'','msg'=>'','data'=>'')
         */        
        public function check_in_select()
        {

            $date_start = $_POST['date_start'];     //开始时间
            $date_end = $_POST['date_end'];         //截至时间
            $type = $_POST['type'];                 //类型
            $name = $_POST['name'];                 //人员名称
            $jobNumber = $_POST['jobNumber'];       //工号
            $limit = $_POST['limit'];               //页数

            // $date_start = "";
            // $date_end = "";
            // $type = "0";
            // $name = "郝军强";
            // $jobNumber = "";
            // $limit = "1";

            $result_json = $this->Register_manage->check_in_select(
                $date_start, 
                $date_end, 
                $type, 
                $name, 
                $jobNumber, 
                $limit
            );

            echo $result_json;

            return 0;
        }

        
        /**
         * @description: 退宿人员信息方法
         * @param {name, jobNumber}
         * @return json:array('res'=>'','msg'=>'','data'=>'')
         */        
        public function retreat_select()
        {

            $name = $_POST['name'];             //姓名
            $jobNumber = $_POST['jobNumber'];   //工号

            // $name = "袁婷";                
            // $jobNumber = "";          

            $result_json = $this->Retreat->retreat_select(
                $name,
                $jobNumber
            );

            echo $result_json;

            return 0;
        }


        /**
         * @description:入住/调宿/退宿人员查询方法 
         * @param {date_start, date_end,type, name, jobNumber, limit}
         * @return json:array('res'=>'','msg'=>'','data'=>'')
         */        
        public function dorm_manage_select()
        {
            
            $date_start = $_POST['date_start'];  //开始日期
            $date_end = $_POST['date_end'];      //结束日期
            $type = $_POST['type'];              //查询类型
            $name = $_POST['name'];              //姓名  
            $jobNumber = $_POST['jobNumber'];    //工号
            $limit = $_POST['limit'];            //页数

            // $date_start = "";
            // $date_end = "";
            // $type = "";
            // $name = ""; 
            // $jobNumber = "";
            // $limit = "";

            $count = $this->Register_manage->dorm_manage_select_all(
                $date_start, 
                $date_end, 
                $type, 
                $name, 
                $jobNumber, 
                $limit
            );

            $result_json = $this->Register_manage->dorm_manage_select(
                $date_start, 
                $date_end, 
                $type, 
                $name, 
                $jobNumber, 
                $limit,
                $count
            );

            echo $result_json;

            return 0;
        }

        /**
         * @description: 退宿领用物品查询
         * @param {jobNumber}
         * @return json:array('res'=>'','msg'=>'','data'=>'')
         */        
        public function use_effects_select()
        {

            $jobNumber = $_POST['jobNumber'];  //工号

            // $jobNumber = "IT00053";

            $result_json = $this->Retreat->use_effects_select($jobNumber);

            echo $result_json;

            return 0;
        }

        // /**
        //  * @description: 退宿方法
        //  * @param {name,  jobNumber,check_inAddress,retreatDate,retreatType,room_hotwater,room_coldwater,room_electric,remarks}
        //  * @return {*}
        //  */            
        // public function retreat()
        // {

        //     $name = $_POST['name'];                         //姓名
        //     $jobNumber = $_POST['jobNumber'];               //工号
        //     $check_inAddress = $_POST['check_inAddress'];   //入住位置
        //     $retreatDate = $_POST['retreatDate'];           //退宿日期
        //     $retreatType = $_POST['retreatType'];           //退宿类型
        //     $room_hotwater = $_POST['room_hotwater'];       //房间热水表
        //     $room_coldwater = $_POST['room_coldwater'];     //房间冷水表
        //     $room_electric = $_POST['room_electric'];       //房间电表
        //     $remarks = $_POST['remarks'];                   //备注

            
        //     // $name = "杜明忠";
        //     // $jobNumber = "A0694";
        //     // $check_inAddress = "1585*4*417*2";
        //     // $retreatDate = "2021-03-15";
        //     // $retreatType = "1";
        //     // $room_hotwater = "100";
        //     // $room_coldwater = "100";
        //     // $room_electric = "100";
        //     // $remarks = "测试";

        //     $result_json = $this->Retreat->retreat(
        //         $name,  
        //         $jobNumber,
        //         $check_inAddress,
        //         $retreatDate,
        //         $retreatType,
        //         $room_hotwater,
        //         $room_coldwater,
        //         $room_electric,
        //         $remarks
        //     );

        //     echo $result_json;

        //     return 0;
        // }

                /**
         * @description: 退宿方法
         * @param {name,  jobNumber, check_inAddress, retreatDate, retreatType, remarks}
         * @return json:array('res'=>'','msg'=>'','data'=>'')
         */            
        public function retreat()
        {

            $name = $_POST['name'];                         //姓名
            $jobNumber = $_POST['jobNumber'];               //工号
            $idNumber = $_POST['idNumber'];                 //身份证号
            $check_inAddress = $_POST['check_inAddress'];   //入住位置
            $retreatDate = $_POST['retreatDate'];           //退宿日期
            $retreatType = $_POST['retreatType'];           //退宿类型
            $room_hotwater = $_POST['room_hotwater'];       //房间热水表
            $room_coldwater = $_POST['room_coldwater'];     //房间冷水表
            $room_electric = $_POST['room_electric'];       //房间电表
            $type = $_POST['type'];                         //0.月底结算1.退宿2.调宿
            $remarks = $_POST['remarks'];                   //备注

            // $name = "张倩楠";
            // $jobNumber = "11754";
            // $idNumber = "340621200010015644";
            // $check_inAddress = "1585*1*301-2*1";
            // $retreatDate = "2021-04-23";
            // $retreatType = "1";
            // $room_hotwater = "0";
            // $room_coldwater = "0";
            // $room_electric = "0";
            // $type = "1";
            // $remarks = "测试";

            //测试先将水电计算注释
            
            // 取入住位置
            // $live = $this->ApplicationForm_model->liveAddress($jobNumber, $idNumber);
            // $live = json_decode($live);

            // $roomMeterArray = array(
            //     'jobNumber' => $jobNumber,
            //     'idNumber' => $idNumber, 
            //     'date' => $retreatDate,
            //     'area_number' => $live->data->area_number, 
            //     'floor_number' => $live->data->floor_number, 
            //     'dorm_floor' => $live->data->dorm_floor,
            //     'room_number' => $live->data->room_number, 
            //     'room_hotwater' => $room_hotwater, 
            //     'room_coldwater' => $room_coldwater, 
            //     'room_electric' => $room_electric, 
            //     'type' => $type
            // );

            // //查询是否有上月的水电表数据，若没有则不允许进行结算
            // $lastMonth = $this->Retreat->lastMonth($retreatDate, $live->data->area_number, $live->data->floor_number, $live->data->dorm_floor, $live->data->room_number);
            // $lastMonth = json_decode($lastMonth);
            
            //     //个人水电计算
            //     $resultCost_json = $this->singleUtilitiesCalc($roomMeterArray);
            //     $resultCost_json = json_decode($resultCost_json);
            //     if($resultCost_json->res == '40000')
            //     {
            //         $result_json = $this->Retreat->retreat(
            //             $name,  
            //             $idNumber,
            //             $jobNumber,
            //             $check_inAddress,
            //             $retreatDate,
            //             $retreatType,
            //             $remarks
            //         );
            //         $result_json = json_decode($result_json);
            //         if($result_json->res == '40000')
            //         {
            //             $result_json1 = json_encode(array('res' => '40000', 'msg' => '退宿成功', 'data' => $resultCost_json->data));
            //             echo $result_json1;
            //         }
            //         else
            //         {
            //             $result_json = json_encode($result_json);
            //             echo $result_json;
            //         }
            //     }
            //     else
            //     {
            //         $resultCost_json = json_encode($resultCost_json);
            //         echo $resultCost_json;
            //     }
                
            $result_json = $this->Retreat->retreat(
                $name,  
                $idNumber,
                $jobNumber,
                $check_inAddress,
                $retreatDate,
                $retreatType,
                $remarks
            );

            echo $result_json ;

            return 0;
        }

        /**
         * @description: 退宿/换宿人员费用
         * @param {area_number, floor_number, dorm_floor, room_number, date}
         * @return json:array('res'=>'','msg'=>'','data'=>'')
         */    
        public function personCost($area_number, $floor_number, $dorm_floor, $room_number, $date)
        {   
            $cost_hotwater = 0;
            $cost_coldwater = 0;
            $cost_electric = 0;

            // 退宿人员费用
            $retreatCost = $this->ApplicationForm_model->retreatCost($area_number, $floor_number, $dorm_floor, $room_number, $date);

            $retreatCost = json_decode($retreatCost);

            $retreatCost = $retreatCost->data;

            // 换宿人员费用
            $tuneinCost = $this->ApplicationForm_model->tuneinCost($area_number, $floor_number, $dorm_floor, $room_number, $date);

            $tuneinCost = json_decode($tuneinCost);

            $tuneinCost = $tuneinCost->data;

            if($retreatCost != FALSE)
            {
                //退宿汇总
                for($i=0; $i<count($retreatCost); $i++)
                {
                    //热水费汇总
                    $cost_hotwater += ($retreatCost[$i]->person_hotwater);

                    //冷水费汇总
                    $cost_coldwater += ($retreatCost[$i]->person_coldwater);

                    //电费汇总
                    $cost_electric += ($retreatCost[$i]->person_electric);
                }
            }
        
            if($tuneinCost != FALSE)
            {
                //换宿汇总
                for($i=0; $i<count($tuneinCost); $i++)
                {
                    //热水费汇总
                    $cost_hotwater += ($tuneinCost[$i]->person_hotwater);

                    //冷水费汇总
                    $cost_coldwater += ($tuneinCost[$i]->person_coldwater);

                    //电费汇总
                    $cost_electric += ($tuneinCost[$i]->person_electric);
                }
            }

            $cost = array(
                'area_number' => $area_number,
                'floor_number' => $floor_number,
                'dorm_floor' => $dorm_floor,
                'room_number' => $room_number,
                'cost_hotwater' => $cost_hotwater,
                'cost_coldwater' => $cost_coldwater,
                'cost_electric' => $cost_electric,
                );
            return $cost;
        }

        /**
         * @description: 保存人天表
         * @param {area_number, floor_number, dorm_floor, room_number, jobNumber, idNumber, liveDays, type, date}
         * @return json:array('res'=>'','msg'=>'','data'=>'')
         */    
        public function savemanday($area_number, $floor_number, $dorm_floor, $room_number, $jobNumber, $idNumber ,$liveDays, $type, $date)
        {
            $arr = [];

            //根据工号获取入住位置
            // $liveAddress = $this->ApplicationForm_model->liveAddress($jobNumber);

            // $liveAddress = json_decode($liveAddress);

            // $liveAddress = $liveAddress->data;

            //     $area_number = $liveAddress->area_number;
            //     $floor_number = $liveAddress->floor_number;
            //     $dorm_floor = $liveAddress->dorm_floor;
            //     $room_number = $liveAddress->room_number;

            $cost_date = substr($date, 5, 2);
            
            //更新到人天表
            $manday = array(
                'area_number' => $area_number,
                'floor_number' => $floor_number,
                'dorm_floor' => $dorm_floor,
                'room_number' => $room_number,
                // 'idNumber' => $idNumber,
                'jobNumber' => $jobNumber,
                'liveDays' => $liveDays,
                'date' => date("Y-$cost_date-25"),
                'type' => $type
            );

            $result = $this->ApplicationForm_model->savemanday($manday);

            if($result)
            {
                return array('res' => '40000', 'msg' => '更新成功', 'data' => '');
            }
            else
            {
                return array('res' => '40001', 'msg' => '更新失败', 'data' => '');
            }
        }

        /**
         * @description: 计算每人人天
         * @param {jobNumber, idNumber, date}
         * @return json:array('res'=>'','msg'=>'','data'=>'')
         */    
        public function calcSignleDay($jobNumber = FALSE, $idNumber = FALSE, $date = FALSE)
        {
            $apply_days = 0;
            $leaveOff_days = 0;
            
            //拉取入住日期
            $liveDate = $this->ApplicationForm_model->liveDay($jobNumber, $idNumber);
            //若入住日期与截止日期同年同月则入住天数 = 截止日期 - 入住日期
            $liveDate = json_decode($liveDate);
            $liveDate = $liveDate->data;
            
            if(substr($liveDate->liveDate, 0, 4) == date("Y") && substr($liveDate->liveDate, 5, 2) == date("m"))
            {
                $liveDays = ( strtotime($date) - strtotime( $liveDate->liveDate ) ) / 86400;   
            }
            //若入住天数与当前同年不同月则入住天数 = 输入日期 - 当年当月第一天
            else
            {
                $liveDays = ( strtotime($date)- strtotime(date('Y-m-01', strtotime($date))) ) / 86400; 
            }
            
            //拉取请假单
            $applyDays = $this->ApplicationForm_model->apply($jobNumber, $date);

            $applyDays = json_decode($applyDays);

            $applyDays = $applyDays->data;

            if($applyDays != FALSE)
            {
                for($i=0; $i<count($applyDays); $i++)
                {
                    $apply_days += $applyDays[$i]->apply_days;
                }
            }

            $liveDays = $liveDays - $apply_days;


            //拉取销假单
            $leaveOff = $this->ApplicationForm_model->leaveOff($jobNumber, $date);

            $leaveOff = json_decode($leaveOff);

            $leaveOff = $leaveOff->data;

            if($leaveOff != FALSE)
            {
                for($j=0 ;$j<count($leaveOff); $j++)
                {
                    $leaveOff_days += $leaveOff[$i]->apply_days;
                }
            }
            $liveDays = $liveDays + $leaveOff_days;      
            return $liveDays;
        }
        
        /**
         * @description: 个人水电费计算
         * @param {roomMeterArray}
         * @return {*}
         */
        public function singleUtilitiesCalc($roomMeterArray)
        {
            /**
             * 查询当月期初电表、冷水表、热水表度数（上月月底）
             * 保存当前条记录
             * 计算冷水费、热水费、电费
             * 计算当前房间每人当月入住天数
             * 拉取当月房间内退宿、离职人员费用
             * 拉取均摊费用单价、热水费单价
             */
            // if
            // (   empty($roomMeterArray['jobNumber']) ||
            //     empty($roomMeterArray['date']) ||
            //     empty($roomMeterArray['area_number']) ||
            //     empty($roomMeterArray['floor_number']) ||
            //     empty($roomMeterArray['dorm_floor']) ||
            //     empty($roomMeterArray['room_number']) ||
            //     empty($roomMeterArray['room_hotwater']) ||
            //     empty($roomMeterArray['room_coldwater']) ||
            //     empty($roomMeterArray['room_electric']) ||
            //     empty($roomMeterArray['type'])
            // )
            if
            (   $roomMeterArray['type'] == '' ||
                $roomMeterArray['area_number'] == '' ||
                $roomMeterArray['floor_number'] == '' ||
                $roomMeterArray['dorm_floor'] == '' ||
                $roomMeterArray['room_number'] == '' ||
                $roomMeterArray['room_hotwater'] == '' ||
                $roomMeterArray['room_coldwater'] == '' ||
                $roomMeterArray['room_electric'] == '' ||
                $roomMeterArray['room_electric'] == ''
            )
    
            {
                return json_encode(array('res' => '41001', 'msg' => '数据异常', 'data' =>''));
            }
            else
            {
    
                $sumDays = 0;
                $singleDays = 0;
    
                // $roomMeterArray['jobNumber'] = $_POST['jobNumber'];
                // $roomMeterArray['date'] = $_POST['date'];
                // $roomMeterArray['area_number'] = $_POST['area_number'];
                // $roomMeterArray['floor_number'] = $_POST['floor_number'];
                // $roomMeterArray['dorm_floor'] = $_POST['dorm_floor'];
                // $roomMeterArray['room_number'] = $_POST['room_number'];
                // $roomMeterArray['room_hotwater'] = $_POST['room_hotwater'];
                // $roomMeterArray['room_coldwater'] = $_POST['room_coldwater'];
                // $roomMeterArray['room_electric'] = $_POST['room_electric'];
                // $roomMeterArray['type'] = $_POST['type'];
    
                // 查询当月期初电表、冷水表、热水表度数（上月月底）
                //  $lastMonth = date('Y-m-d', strtotime("-1 day",strtotime(date('Y-m-01', strtotime(date('Y-m-d', $roomMeterArray['date']))))));
                
                // $roomMeter = $this->ApplicationForm_model->getRoomMeter($roomMeterArray['area_number'], $roomMeterArray['floor_number'], $roomMeterArray['dorm_floor'],  $roomMeterArray['room_number'], $lastMonth);
                
                //核算月上月第一天
    
                $monthBegin = date("Y-m-01", (strtotime("-1 month", strtotime($roomMeterArray['date']))));;
                
                //核算月上月最后一天
                $monthEnd = date("Y-m-t", (strtotime("-1 month", strtotime($roomMeterArray['date']))));
    
                $roomMeter = $this->ApplicationForm_model->getRoomMeter($roomMeterArray['area_number'], $roomMeterArray['floor_number'], $roomMeterArray['dorm_floor'],  $roomMeterArray['room_number'], $monthBegin, $monthEnd, 1);//type:1 非公共房间
                $roomMeter = json_decode($roomMeter);
    
                if($roomMeter->res === '40000')
                {
    
                    $roomMeter = $roomMeter->data;
    
                }
    
                // 保存当前条房间水电表记录
                $roomMeterSaveResult = $this->ApplicationForm_model->saveRoomMeter($roomMeterArray['date'], $roomMeterArray['jobNumber'], $roomMeterArray['floor_number'], $roomMeterArray['dorm_floor'], $roomMeterArray['room_number'], $roomMeterArray['room_hotwater'], $roomMeterArray['room_coldwater'], $roomMeterArray['room_electric'], 1);
    
                // 拉取公摊费用系数、热水费单价
                // $month = date('m',$roomMeterArray['date']);
                // $month = $month - 1;
                // $year = date('Y',$roomMeterArray['date']);
    
                // $capitation = $this->ApplicationForm_model->getCapitation($roomMeterArray['area_number'], $roomMeterArray['floor_number'], $roomMeterArray['dorm_floor'], $year, $month);
    
                $capitation = $this->ApplicationForm_model->getCapitation($roomMeterArray['area_number'], $roomMeterArray['floor_number'], $roomMeterArray['dorm_floor'], $roomMeterArray['date']);
                
                $capitation = json_decode($capitation);

                if($capitation->res === '40000')
                {
    
                    $capitation = $capitation->data;
    
                }
    
                // 计算冷水费、热水费、电费
                // 冷水费 = ( 当前水表度数 - 月初水表度数 ) * 单价 7
                if($roomMeterArray['room_coldwater'] == 0)
                {
                    $coldWaterFee = 0;
                }
                else
                {
                    if($roomMeter->res == '40002')
                    {
                        $coldWaterFee = $roomMeterArray['room_coldwater'] * 7;
                    }
                    else
                    {
                        $coldWaterFee = ( $roomMeterArray['room_coldwater'] - $roomMeter->room_coldwater ) * 7;
                    }
                }
                
                if($roomMeterArray['room_electric'] == 0)
                {
                    $electricFee = 0;
                }
                else
                {
                    if($roomMeter->res == '40002')
                    {
                        $electricFee = $roomMeterArray['room_electric'];
                    }
                    else
                    {
                        // 电费 = ( 当前电表度数 - 月初电表度数 ) * 单价 1
                        $electricFee = ( $roomMeterArray['room_electric'] - $roomMeter->room_electric ) * 1;
                    }
                }
                
                if($roomMeterArray['room_hotwater'] == 0)
                {
                    $hotWaterFee = 0;
                }
                else
                {
                    if($roomMeter->res == '40002')
                    {
                        if($capitation->res == '40002')
                        {
                            $hotWaterFee = 0;
                        }
                        else
                        {
                            $hotWaterFee = ( $roomMeterArray['room_hotwater'] ) * $capitation->hotwater_price;
                        }
                    }
                    else
                    {
                        if($capitation->res == '40002')
                        {
                            // 热水费 = ( 当前热水表度数 - 月初热水表度数 ) * 单价
                            $hotWaterFee = ( $roomMeterArray['room_hotwater'] - $roomMeter->room_hotwater );
                        }
                        else
                        {
                            // 热水费 = ( 当前热水表度数 - 月初热水表度数 ) * 单价
                            $hotWaterFee = ( $roomMeterArray['room_hotwater'] - $roomMeter->room_hotwater ) * $capitation->hotwater_price;
                        }
                    }
                }
    
                // 计算当前房间人天
    
                // 拉取房间当前入住人员
                $roomInfo = $this->ApplicationForm_model->room_livepeople($roomMeterArray['area_number'], $roomMeterArray['floor_number'], $roomMeterArray['dorm_floor'],  $roomMeterArray['room_number']);
        
                $roomInfo = json_decode($roomInfo);
                $roomInfo = $roomInfo->data;

                // 计算每个人入住天数
                // for ($i=0; $i < count($roomInfo); $i++) 
                // { 
                //     $days = $this->calcSignleDay($roomInfo[$i]->jobNumber, $roomMeterArray['date']);
                //     $sumDays += $days; // 房间总人天 
    
                //     if( $roomInfo[$i]->jobNumber == $roomMeterArray['jobNumber'] )
                //     {
    
                //         $singleDays = $days;
    
                //     }
                //     $days = 0;
    
                // }
                for ($i=0; $i < count($roomInfo); $i++) 
                { 
                    $days = $this->calcSignleDay($roomInfo[$i]->jobNumber, $roomInfo[$i]->idNumber, $roomMeterArray['date']);
                    $sumDays += $days; // 房间总人天 
    
                    if( $roomInfo[$i]->jobNumber == $roomMeterArray['jobNumber'] OR $roomInfo[$i]->idNumber == $roomMeterArray['idNumber'])
                    {
    
                        $singleDays = $days;
    
                    }
                    $days = 0;
                }
    
                // foreach ($roomInfo as $roomInfoRow) 
                // {
                //     $days = $this->calcSignleDay($roomInfoRow->jobNumber, $roomMeterArray['date']);
    
                //     $sumDays += $days; // 房间总人天
    
                //     if( $roomInfoRow->jobNumber == $roomMeterArray['jobNumber'] )
                //     {
    
                //         $singleDays = $days;
    
                //     }
                
                //     $days = 0;
    
                // }
    
                // 保存人天表
                // $this->savemanday($roomMeterArray['area_number'], $roomMeterArray['floor_number'], $roomMeterArray['dorm_floor'], $roomMeterArray['room_number'], $roomMeterArray['jobNumber'], $singleDays, $roomMeterArray['type'], $roomMeterArray['date']);
                // 保存人天表
                $this->savemanday($roomMeterArray['area_number'], $roomMeterArray['floor_number'], $roomMeterArray['dorm_floor'], $roomMeterArray['room_number'], $roomMeterArray['jobNumber'], $roomMeterArray['idNumber'], $singleDays, $roomMeterArray['type'], $roomMeterArray['date']);
    
                // 拉取退宿、换宿人员费用
                $pastCost = $this->personCost($roomMeterArray['area_number'], 
                $roomMeterArray['floor_number'], $roomMeterArray['dorm_floor'], $roomMeterArray['room_number'],  $roomMeterArray['date']);
    
                // 计算费用
    
                // 冷水/热水/电费 = ( 总费用 - 离职/退宿人员费用 ) / 总人天 * 个人人天
                if($sumDays>0)
                {
                    $cold = ( $coldWaterFee - $pastCost['cost_coldwater'] ) / $sumDays *  $singleDays;
                    $electricity = ( $electricFee - $pastCost['cost_electric'] ) / $sumDays *  $singleDays;
                    $hot = ( $hotWaterFee - $pastCost['cost_hotwater'] ) / $sumDays *  $singleDays;
                }
                else
                {
                    $cold = 0; 
                    $electricity = 0;
                    $hot = 0;
                }

                // 均摊费用 = 均摊费用单价 * 个人人天
                if($capitation->res != '40002')
                {
                    $capitationCold = $capitation->public_coldwater * $singleDays;
                    $capitationHot = $capitation->public_hotwater * $singleDays;
                    $capitationElectricity = $capitation->public_electric * $singleDays;
                }
                else
                {
                    $capitationCold = 0;
                    $capitationHot = 0;
                    $capitationElectricity = 0;
                }
                // 总费用
                $costSum = $cold + $hot + $electricity + $capitationCold + $capitationHot + $capitationElectricity;
    
                $result = array(
                    'cold' => $cold,
                    'hot' => $hot,
                    'electricity' => $electricity,
                    'capitationCold' => $capitationCold,
                    'capitationHot' => $capitationHot,
                    'capitationElectricity' => $capitationElectricity,
                    'costSum' => $costSum,
                    'singleDays' => $singleDays
                );
    
                // // 保存个人费用
                // $singleCost = array(
                //     'cost_date' => $roomMeterArray['date'],
                //     'area_number' => $roomMeterArray['area_number'],
                //     'floor_number' => $roomMeterArray['floor_number'],
                //     'dorm_floor' => $roomMeterArray['dorm_floor'],
                //     'room_number' => $roomMeterArray['room_number'],
                //     'jobNumber' => $roomMeterArray['jobNumber'],
                //     // 'name' => 'zxy',
                //     'month_dayall' => $singleDays,
                //     'person_hotwater' => $hot, 
                //     'person_coldwater' => $cold, 
                //     'person_electric' => $electricity,
                //     'public_hotwater' => $capitationHot, 
                //     'public_coldwater' => $capitationCold,
                //     'public_electric' => $capitationElectricity,
                //     'costAll' => $costSum, 
                //     'type' => $roomMeterArray['type'], 
                // );
                
                // 保存个人费用
                $singleCost = array(
                    'cost_date' => $roomMeterArray['date'],
                    'area_number' => $roomMeterArray['area_number'],
                    'floor_number' => $roomMeterArray['floor_number'],
                    'dorm_floor' => $roomMeterArray['dorm_floor'],
                    'room_number' => $roomMeterArray['room_number'],
                    'jobNumber' => $roomMeterArray['jobNumber'],
                    'idNumber' => $roomMeterArray['idNumber'],
                    'month_dayall' => $singleDays,
                    'person_hotwater' => $hot, 
                    'person_coldwater' => $cold, 
                    'person_electric' => $electricity,
                    'public_hotwater' => $capitationHot, 
                    'public_coldwater' => $capitationCold,
                    'public_electric' => $capitationElectricity,
                    'costAll' => $costSum, 
                    'type' => $roomMeterArray['type'], 
                );
                $saveSingleFee = $this->ApplicationForm_model->saveSingleFee($singleCost);
            }
    
            if($result)
            {
                $return_su = array('res' => '40000', 'msg' => '查询成功', 'data' => $result);
                $result = json_encode($return_su);
            }
            else
            {
                $return_fa = array('res' => '40001', 'msg' => '查询无数据', 'data' => '');
                $result = json_encode($return_fa);
            }
            return $result;
        }

        /**
         * @description:调宿人员查询方法 
         * @param {name, jobNumber}
         * @return json:array('res'=>'','msg'=>'','data'=>'')
         */            
        public function  tune_in_select()
        {
            $name = $_POST['name'];             //姓名
            $jobNumber = $_POST['jobNumber'];   //工号

            // $name = "张豆豆";
            // $jobNumber = "R015971";

            $result_json = $this->Tune_in->tune_in_select(
                $name,
                $jobNumber
            );

            echo $result_json;

            return 0;
        }

        /**
         * @description:调换宿舍信息查询方法:取区域 
         * @param {*}
         * @return json:array('res'=>'','msg'=>'','data'=>'')
         */        
        public function tune_in_area()
        {

            $result_json = $this->Tune_in->tune_in_area();

            echo $result_json;

            return 0;
        }

        /**
         * @description: 调换宿舍信息查询方法:取宿舍楼
         * @param {area_number}
         * @return json:array('res'=>'','msg'=>'','data'=>'')
         */        
        public function tune_in_dorm()
        {
            $area_number = $_POST['area_number'];   //区域编号

            // $area_number = "1585";

            $result_json = $this->Tune_in->tune_in_dorm($area_number);

            echo $result_json;

            return 0;
        }

        /**
         * @description:调换宿舍信息查询方法:取楼层 
         * @param {area_number, floor_number}
         * @return json:array('res'=>'','msg'=>'','data'=>'')
         */        
        public function tune_in_floor()
        {

            $area_number = $_POST['area_number'];       //区域编号
            $floor_number = $_POST['floor_number'];     //宿舍楼编号

            // $area_number = "1585";
            // $floor_number = "1";  

            $result_json = $this->Tune_in->tune_in_floor(
                $area_number,
                $floor_number
            );
            
            // $str = '';
            // for($i=1 ;$i<=$result_json->SSL_floor ;$i++){
            //     $str = $str.$i.'*';
            // }
            // $str = substr($str ,0 ,-1);

            // $return_su = array('res' => '40000', 'msg' => '查询成功', 'data' => $str);
            // $result = json_encode($return_su);
            
            echo $result_json;

            return 0;
        }

        /**
         * @description: 调换宿舍信息查询方法:取宿舍
         * @param {area_number, floor_number, dorm_floor }
         * @return json:array('res'=>'','msg'=>'','data'=>'')
         */        
        public function tune_in_room()
        {

            $area_number = $_POST['area_number'];    //区域编号
            $floor_number = $_POST['floor_number'];  //宿舍楼编号
            $dorm_floor = $_POST['dorm_floor'];      //宿舍楼层号

            // $area_number = "1585";      
            // $floor_number = "1";       
            // $dorm_floor =   "3";        

            $result_json = $this->Tune_in->tune_in_room(
                $area_number,
                $floor_number,       
                $dorm_floor   
            );

            echo $result_json;

            return 0;

        }

        /**
         * @description:调换宿舍信息查询方法:取床位 
         * @param {area_number, floor_number, room_number}
         * @return {*}
         */        
        public function tune_in_bed()
        {

            $area_number = $_POST['area_number'];       //区域编号
            $floor_number = $_POST['floor_number'];     //宿舍楼编号
            $room_number = $_POST['room_number'];       //宿舍房间号

            // $area_number = "333";  
            // $floor_number = "3";
            // $room_number = "101";  

            $result_json = $this->Tune_in->tune_in_bed(
                $area_number,
                $floor_number,
                $room_number
            );

            echo $result_json;

            return 0;
        }

        /**
         * @description: 调换宿舍信息查询方法：取柜子/判断床位
         * @param {area_name, floor_name, dorm_floor, room_number, bedNumber}
         * @return {*}
         */        
        public function tune_in_isbed()
        {

            $area_name = $_POST['area_name'];       //区域名称
            $floor_name = $_POST['floor_name'];     //宿舍楼名称
            $dorm_floor = $_POST['dorm_floor'];     //宿舍楼层号
            $room_number = $_POST['room_number'];   //宿舍房间号
            $bedNumber = $_POST['bedNumber'];       //床位号

            // $area_name = "";
            // $floor_name = "";
            // $dorm_floor = "";
            // $room_number = "";
            // $bedNumber = "";

            $result_json = $this->Tune_in->tune_in_isbed(
                $area_name,
                $floor_name,
                $dorm_floo,
                $room_number,
                $bedNumber
            );

            echo $result_json;

            return 0;
        }

        
        /**
         * @description:调换宿舍信息查询方法：判断柜子 
         * @param {idNumber, room_number, area_name, floor_name, dorm_floor, wardrobe}
         * @return {*}
         */        
        public function tune_in_wardrobe()
        {

            $idNumber = $_POST['idNumber'];         //身份证号
            $room_number = $_POST['room_number'];   //宿舍房间号
            $area_name = $_POST['area_name'];       //区域名称
            $floor_name = $_POST['floor_name'];     //宿舍楼名称
            $dorm_floor = $_POST['dorm_floor'];     //宿舍楼层号
            $wardrobe = $_POST['wardrobe'];         //柜子号

            // $idNumber = "";
            // $room_number = "";
            // $area_name = "";
            // $floor_name = "";
            // $dorm_floor = "";
            // $wardrobe = "";

            $result_json = $this->Tune_in->tune_in_wardrobe(
                $idNumber,
                $room_number,
                $area_name,
                $floor_name,
                $dorm_floor,
                $wardrobe
            );

            echo $result_json;

            return 0;
        }

        // /**
        //  * @description: 调宿方法
        //  * @param {CoordinatingDate,name,jobNumber,sex,department,check_inAddress,area_number,floor_number,room_number,bedNumber,room_hotwater,room_coldwater,room_electric,remarks}
        //  * @return {*}
        //  */        
        public function tune_in()
        {

            $CoordinatingDate = $_POST['CoordinatingDate'];       //调宿日期
            $name = $_POST['name'];                               //人员姓名
            $jobNumber = $_POST['jobNumber'];                     //工号
            $idNumber = $_POST['idNumber'];                       //身份证号
            $sex = $_POST['sex'];                                 //性别
            $department1 = $_POST['department1'];                 //一级部门
            $department2 = $_POST['department2'];                 //二级部门
            $department3 = $_POST['department3'];                 //三级部门
            $check_inAddress = $_POST['check_inAddress'];         //入住位置
            $area_number = $_POST['area_number'];                 //区域编号
            $floor_number = $_POST['floor_number'];               //宿舍楼编号
            $room_number = $_POST['room_number'];                 //宿舍房间号
            $bedNumber = $_POST['bedNumber'];                     //床位号
            // $room_hotwater = $_POST['room_hotwater'];          //房间热水表
            // $room_coldwater = $_POST['room_coldwater'];        //房间冷水表
            // $room_electric = $_POST['room_electric'];          //房间电表
            $remarks = $_POST['remarks'];                         //备注


        //     $CoordinatingDate = "2020-11-18";
        //     $name = "王林";
        //     $jobNumber = "R513499";
        //     $idNumber = "";
        //     $sex = "1";
        //     $department1 = "";
        //     $department2 = "";
        //     $department3 = "";
        //     $check_inAddress = "1585*2*B08*1";
        //     $area_number = "1585";
        //     $floor_number = "1";
        //     $room_number = "452";
        //     $bedNumber = "6";
        //     $room_hotwater = "100";
        //     $room_coldwater = "100";
        //     $room_electric = "100";
        //     $remarks = "测试";

            // $query_retreatCost  = $this->Tune_in->tune_in(
            //     $CoordinatingDate,
            //     $name,
            //     $jobNumber,
            //     $idNumber,
            //     $sex,
            //     $department1,
            //     $department2,
            //     $department3,
            //     $check_inAddress,
            //     $area_number,
            //     $floor_number,
            //     $room_number,
            //     $bedNumber,
            //     $room_hotwater,
            //     $room_coldwater,
            //     $room_electric,
            //     $remarks
            // );

            $result_json  = $this->Tune_in->tune_in(
                $CoordinatingDate,
                $name,
                $jobNumber,
                $idNumber,
                $sex,
                $department1,
                $department2,
                $department3,
                $check_inAddress,
                $area_number,
                $floor_number,
                $room_number,
                $bedNumber,
                $room_hotwater,
                $room_coldwater,
                $room_electric,
                $remarks
            );
            
        //     $query_retreatCost  = json_decode($query_retreatCost);

        //     //调用方法计算水电费
        //     $result_json = $this->ApplicationForm->singleUtilitiesCalc($query_retreatCost);
        //     $result_json = json_decode($result_json);

            echo $result_json;

            return 0;
        }
    

    /**
     * @description: 入住/退宿/调宿记录excel导出
     * @param {*}
     * @return {*}
     */    
    public function excel_Register_manage()
    {
        // 创建一个处理对象实例   
        $objExcel = new \PHPExcel();  
        // 创建文件格式写入对象实例, uncomment   
        $objProps = $objExcel->getProperties();  
        //设置创建者  TODO:
        $objProps->setCreator('admin');
        //描述  
        // $objProps->setDescription("入住信息汇总");  
        $objActSheet = $objExcel->getActiveSheet();
        //单元格赋值
        $objActSheet->setCellValue('A1','姓名');
        $objActSheet->setCellValue('B1','工号');
        $objActSheet->setCellValue('C1','性别');
        $objActSheet->setCellValue('D1','部门');
        $objActSheet->setCellValue('E1','岗位');         
        $objActSheet->setCellValue('F1','区域名称');
        $objActSheet->setCellValue('G1','宿舍楼名称');
        $objActSheet->setCellValue('H1','宿舍房间号');
        $objActSheet->setCellValue('I1','床位号');
        $objActSheet->setCellValue('J1','类型');
        $objActSheet->setCellValue('K1','日期');

        // $objActSheet->setCellValue('J1','是否入住');
        // $objActSheet->setCellValue('K1','入住时间');
        // $objActSheet->setCellValue('L1','是否退宿');
        // $objActSheet->setCellValue('M1','退宿时间');
        $num = 2;
        
        $data = $this->Register_manage->excel_record();
        $data = json_decode($data,true);
        
        $data_livepeople = [];
        $data_excel = [];

        for($i=0; $i<count($data['msg']); $i++)
        {
            $data_livepeople['name'] = $data['msg'][$i]['name'];
            $data_livepeople['jobNumber'] = $data['msg'][$i]['jobNumber'];
            if($data['msg'][$i]['sex'] === '0')
            {
                $data_livepeople['sex'] = '女';
            }
            else
            {
                $data_livepeople['sex'] = '男';
            }
            $data_livepeople['department'] = $data['msg'][$i]['department'];
            $data_livepeople['job'] = $data['msg'][$i]['job'];
            $data_livepeople['area_name'] = $data['msg'][$i]['area_name'];
            $data_livepeople['floor_name'] = $data['msg'][$i]['floor_name'];
            $data_livepeople['room_number'] = $data['msg'][$i]['room_number'];
            $data_livepeople['bedNumber'] = $data['msg'][$i]['bedNumber'];
            if($data['msg'][$i]['type'] === '1')
            {
                $data_livepeople['type'] = '入住';
                $data_livepeople['date'] = $data['msg'][$i]['liveDate'];
            }
            elseif($data['msg'][$i]['type'] === '2')
            {
                $data_livepeople['type'] = '调宿';
                $data_livepeople['date'] = $data['msg'][$i]['changeDate'];
            }
            elseif($data['msg'][$i]['type'] === '0' )
            {
                $data_livepeople['type'] = '退宿';
                $data_livepeople['date'] = $data['msg'][$i]['retreatDate'];
            }

            array_push($data_excel, $data_livepeople);
        }
        // for ($i=0; $i < count($data['msg']); $i++) 
        // { 
        //     //性别
        //     if($data['msg'][$i]['sex'] == 0)
        //     {
        //         $data['msg'][$i]['sex'] = '女';
        //     } 
        //     else 
        //     {
        //         $data['msg'][$i]['sex'] = '男';
        //     }
            
        //     //是否入住
        //     if($data['msg'][$i]['liveCheck'] == 'X')
        //     {
        //         $data['msg'][$i]['liveCheck'] = '已入住';
        //     } 
        //     else 
        //     {
        //         $data['msg'][$i]['liveCheck'] = '';
        //     }

        //     //是否退宿
        //     if($data['msg'][$i]['quitCheck'] == 'X')
        //     {
        //         $data['msg'][$i]['quitCheck'] = '已退宿';
        //     } 
        //     else 
        //     {
        //         $data['msg'][$i]['quitCheck'] = '';
        //     }
        // }
        
        // for ($i=0; $i < count($data['msg']); $i++) 
        // {

        //     $objActSheet->setCellValueExplicit('A' . $num,$data['msg'][$i]['name'] , PHPExcel_Cell_DataType::TYPE_STRING);            //姓名
        //     $objActSheet->setCellValueExplicit('B' . $num,$data['msg'][$i]['jobNumber'] , PHPExcel_Cell_DataType::TYPE_STRING);       //工号
        //     $objActSheet->setCellValueExplicit('C' . $num,$data['msg'][$i]['sex'] , PHPExcel_Cell_DataType::TYPE_STRING);             //性别
        //     $objActSheet->setCellValueExplicit('D' . $num,$data['msg'][$i]['department'] , PHPExcel_Cell_DataType::TYPE_STRING);      //部门
        //     $objActSheet->setCellValueExplicit('E' . $num,$data['msg'][$i]['job'] , PHPExcel_Cell_DataType::TYPE_STRING);             //岗位 
        //     $objActSheet->setCellValueExplicit('F' . $num,$data['msg'][$i]['area_name'] , PHPExcel_Cell_DataType::TYPE_STRING);       //区域名称
        //     $objActSheet->setCellValueExplicit('G' . $num,$data['msg'][$i]['floor_name'], PHPExcel_Cell_DataType::TYPE_STRING);       //宿舍楼名称
        //     $objActSheet->setCellValueExplicit('H' . $num,$data['msg'][$i]['room_number'], PHPExcel_Cell_DataType::TYPE_STRING);      //宿舍房间号
        //     $objActSheet->setCellValueExplicit('I' . $num,$data['msg'][$i]['bedNumber'], PHPExcel_Cell_DataType::TYPE_STRING);        //床位号
        //     $objActSheet->setCellValueExplicit('J' . $num,$data['msg'][$i]['liveCheck'], PHPExcel_Cell_DataType::TYPE_STRING);        //是否入住
        //     $objActSheet->setCellValueExplicit('K' . $num,$data['msg'][$i]['liveDate'], PHPExcel_Cell_DataType::TYPE_STRING);         //入住日期
        //     $objActSheet->setCellValueExplicit('L' . $num,$data['msg'][$i]['quitCheck'], PHPExcel_Cell_DataType::TYPE_STRING);        //是够退宿
        //     $objActSheet->setCellValueExplicit('M' . $num,$data['msg'][$i]['retreatDate'], PHPExcel_Cell_DataType::TYPE_STRING);      //退宿日期
        //     //设置行的属性  
        //     $objActSheet->getRowDimension($num)->setRowHeight('20px');//设置行高  
        //     $num = $num + 1;
        // }
        
        // //设置列的属性  
        // $objActSheet->getColumnDimension("A")->setWidth('20px'); // 设置宽度  
        // $objActSheet->getColumnDimension("B")->setWidth('20px'); // 设置宽度  
        // $objActSheet->getColumnDimension("C")->setWidth('20px'); // 设置宽度  
        // $objActSheet->getColumnDimension("D")->setWidth('20px'); // 设置宽度  
        // $objActSheet->getColumnDimension("E")->setWidth('20px'); // 设置宽度  
        // $objActSheet->getColumnDimension("F")->setWidth('20px'); // 设置宽度 
        // $objActSheet->getColumnDimension("G")->setWidth('20px'); // 设置宽度
        // $objActSheet->getColumnDimension("H")->setWidth('20px'); // 设置宽度 
        // $objActSheet->getColumnDimension("I")->setWidth('20px'); // 设置宽度 
        // $objActSheet->getColumnDimension("J")->setWidth('20px'); // 设置宽度 
        // $objActSheet->getColumnDimension("K")->setWidth('20px'); // 设置宽度 
        // $objActSheet->getColumnDimension("L")->setWidth('20px'); // 设置宽度 
        // $objActSheet->getColumnDimension("M")->setWidth('20px'); // 设置宽度 
        // $outputFileName = iconv('UTF-8', 'gb2312', '入住/退宿/调宿记录表.xls');  

        for ($i=0; $i < count($data_excel); $i++) 
        {

            $objActSheet->setCellValueExplicit('A' . $num,$data_excel[$i]['name'] , PHPExcel_Cell_DataType::TYPE_STRING);            //姓名
            $objActSheet->setCellValueExplicit('B' . $num,$data_excel[$i]['jobNumber'] , PHPExcel_Cell_DataType::TYPE_STRING);       //工号
            $objActSheet->setCellValueExplicit('C' . $num,$data_excel[$i]['sex'] , PHPExcel_Cell_DataType::TYPE_STRING);             //性别
            $objActSheet->setCellValueExplicit('D' . $num,$data_excel[$i]['department'] , PHPExcel_Cell_DataType::TYPE_STRING);      //部门
            $objActSheet->setCellValueExplicit('E' . $num,$data_excel[$i]['job'] , PHPExcel_Cell_DataType::TYPE_STRING);             //岗位 
            $objActSheet->setCellValueExplicit('F' . $num,$data_excel[$i]['area_name'] , PHPExcel_Cell_DataType::TYPE_STRING);       //区域名称
            $objActSheet->setCellValueExplicit('G' . $num,$data_excel[$i]['floor_name'], PHPExcel_Cell_DataType::TYPE_STRING);       //宿舍楼名称
            $objActSheet->setCellValueExplicit('H' . $num,$data_excel[$i]['room_number'], PHPExcel_Cell_DataType::TYPE_STRING);      //宿舍房间号
            $objActSheet->setCellValueExplicit('I' . $num,$data_excel[$i]['bedNumber'], PHPExcel_Cell_DataType::TYPE_STRING);        //床位号
            $objActSheet->setCellValueExplicit('J' . $num,$data_excel[$i]['type'], PHPExcel_Cell_DataType::TYPE_STRING);             //类型
            $objActSheet->setCellValueExplicit('K' . $num,$data_excel[$i]['date'], PHPExcel_Cell_DataType::TYPE_STRING);             //日期
            //设置行的属性  
            $objActSheet->getRowDimension($num)->setRowHeight('20px');//设置行高  
            $num = $num + 1;
        }
        
        //设置列的属性  
        $objActSheet->getColumnDimension("A")->setWidth(20); // 设置宽度  
        $objActSheet->getColumnDimension("B")->setWidth(20); // 设置宽度  
        $objActSheet->getColumnDimension("C")->setWidth(20); // 设置宽度  
        $objActSheet->getColumnDimension("D")->setWidth(20); // 设置宽度  
        $objActSheet->getColumnDimension("E")->setWidth(20); // 设置宽度  
        $objActSheet->getColumnDimension("F")->setWidth(20); // 设置宽度 
        $objActSheet->getColumnDimension("G")->setWidth(20); // 设置宽度
        $objActSheet->getColumnDimension("H")->setWidth(20); // 设置宽度 
        $objActSheet->getColumnDimension("I")->setWidth(20); // 设置宽度 
        $objActSheet->getColumnDimension("J")->setWidth(20); // 设置宽度 
        $objActSheet->getColumnDimension("K")->setWidth(20); // 设置宽度 
        $outputFileName = iconv('UTF-8', 'gb2312', '入住/退宿/调宿记录表.xlsx'); 
        
        //文件直接输出到浏览器  
        header('Pragma:public');  
        header('Expires:0');  
        header('Cache-Control:must-revalidate,post-check=0,pre-check=0');  
        header('Content-Type:application/force-download');  
        header('Content-Type:application/vnd.ms-excel');  
        header('Content-Type:application/octet-stream');  
        header('Content-Type:application/download');  
        header('Content-Disposition:attachment;filename=' . $outputFileName);  
        header('Content-Transfer-Encoding:binary');  
        PHPExcel_IOFactory::createWriter($objExcel, 'Excel2007')->save('php://output');
                
        //设置对齐方式      
        $objStyleA3 = $objActSheet->getStyle('A3');  
        $objAlignA3 = $objStyleA3->getAlignment();  
        $objAlignA3->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);  
        $objAlignA3->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
        
        $objExcel = null;
    }
    
    //人员入住天数数据取值
    // public function daysCalc($date)
    public function daysCalc()
    {
        $date = date("Y-m-d");
        $arr_days = [];
        $year = date("Y");
        $month = date("m");
        
        //取没有进行过调宿以及退宿的人员进行天数计算
        $sql = "SELECT * FROM dorm_livepeople WHERE idNumber NOT IN ( SELECT idNumber FROM dorm_days WHERE year = '".$year."' AND month = '".$month."')";
        $query = $this->db->query($sql);
        for($i=0; $i<count($query->result()); $i++)
        {   
            $datetime_start = new DateTime($query->result()[$i]->liveDate);
            $datetime_end = new DateTime($date);
            $days = $datetime_start->diff($datetime_end)->days;
            $query->result()[$i]->days = $days;
            array_push($arr_days, $query->result()[$i]);
        }

        //取本月进行过调宿或者退宿的人员的数据
        $sql_days = "SELECT * FROM dorm_livepeople AS A INNER JOIN dorm_days AS B ON A.idNumber = B.idNumber WHERE year = '".$year."' AND month = '".$month."'";
        $query_days = $this->db->query($sql_days);
        for($j=0; $j<count($query_days->result()); $j++)
        {
            array_push($arr_days, $query_days->result()[$j]);
        }

        return json_encode(array('res' => '40001', 'msg' => $arr_days));
    }

    /**
     * @description: 人员入住天数excel导出
     * @param {*}
     * @return {*}
     */    
    public function excel_days()
    {
        // 创建一个处理对象实例   
        $objExcel = new \PHPExcel();  
        // 创建文件格式写入对象实例, uncomment   
        $objProps = $objExcel->getProperties();  
        //设置创建者  TODO:
        $objProps->setCreator('admin');
        //描述  
        // $objProps->setDescription("入住信息汇总");  
        $objActSheet = $objExcel->getActiveSheet();
        //单元格赋值
        $objActSheet->setCellValue('A1','区域编号');
        $objActSheet->setCellValue('B1','宿舍楼编号');
        $objActSheet->setCellValue('C1','宿舍楼层号');
        $objActSheet->setCellValue('D1','宿舍房间号');
        $objActSheet->setCellValue('E1','姓名');         
        $objActSheet->setCellValue('F1','身份证号');
        $objActSheet->setCellValue('G1','工号');
        $objActSheet->setCellValue('H1','性别');
        $objActSheet->setCellValue('I1','一级部门');
        $objActSheet->setCellValue('J1','二级部门');
        $objActSheet->setCellValue('K1','三级部门');
        $objActSheet->setCellValue('L1','岗位');
        $objActSheet->setCellValue('M1','天数');
        
        $num = 2;
        
        $data = $this->daysCalc();
        $data = json_decode($data,true);
        
        for($i=0; $i<count($data['msg']); $i++)
        {
            if($data['msg'][$i]['sex'] === '0')
            {
                $data['msg'][$i]['sex'] = '女';
            }
            else
            {
                $data['msg'][$i]['sex'] = '男';
            }
        }
    
        for ($i=0; $i < count($data['msg']); $i++) 
        {
            $objActSheet->setCellValueExplicit('A' . $num,$data['msg'][$i]['area_number'] , PHPExcel_Cell_DataType::TYPE_STRING);        //区域编号
            $objActSheet->setCellValueExplicit('B' . $num,$data['msg'][$i]['floor_number'] , PHPExcel_Cell_DataType::TYPE_STRING);       //宿舍楼编号
            $objActSheet->setCellValueExplicit('C' . $num,$data['msg'][$i]['dorm_floor'] , PHPExcel_Cell_DataType::TYPE_STRING);         //宿舍楼层号
            $objActSheet->setCellValueExplicit('D' . $num,$data['msg'][$i]['room_number'] , PHPExcel_Cell_DataType::TYPE_STRING);        //宿舍房间号
            $objActSheet->setCellValueExplicit('E' . $num,$data['msg'][$i]['name'] , PHPExcel_Cell_DataType::TYPE_STRING);               //姓名 
            $objActSheet->setCellValueExplicit('F' . $num,$data['msg'][$i]['idNumber'] , PHPExcel_Cell_DataType::TYPE_STRING);           //身份证号
            $objActSheet->setCellValueExplicit('G' . $num,$data['msg'][$i]['jobNumber'], PHPExcel_Cell_DataType::TYPE_STRING);           //工号
            $objActSheet->setCellValueExplicit('H' . $num,$data['msg'][$i]['sex'], PHPExcel_Cell_DataType::TYPE_STRING);                 //性别
            $objActSheet->setCellValueExplicit('I' . $num,$data['msg'][$i]['department1'], PHPExcel_Cell_DataType::TYPE_STRING);         //一级部门
            $objActSheet->setCellValueExplicit('J' . $num,$data['msg'][$i]['department2'], PHPExcel_Cell_DataType::TYPE_STRING);         //二级部门
            $objActSheet->setCellValueExplicit('K' . $num,$data['msg'][$i]['department3'], PHPExcel_Cell_DataType::TYPE_STRING);         //三级部门
            $objActSheet->setCellValueExplicit('L' . $num,$data['msg'][$i]['job'], PHPExcel_Cell_DataType::TYPE_STRING);                 //岗位
            $objActSheet->setCellValueExplicit('M' . $num,$data['msg'][$i]['days'], PHPExcel_Cell_DataType::TYPE_STRING);                //入住天数
            
            //设置行的属性  
            $objActSheet->getRowDimension($num)->setRowHeight('20px');//设置行高  
            $num = $num + 1;
        }
        
        //设置列的属性  
        $objActSheet->getColumnDimension("A")->setWidth(20); // 设置宽度  
        $objActSheet->getColumnDimension("B")->setWidth(20); // 设置宽度  
        $objActSheet->getColumnDimension("C")->setWidth(20); // 设置宽度  
        $objActSheet->getColumnDimension("D")->setWidth(20); // 设置宽度  
        $objActSheet->getColumnDimension("E")->setWidth(20); // 设置宽度  
        $objActSheet->getColumnDimension("F")->setWidth(20); // 设置宽度 
        $objActSheet->getColumnDimension("G")->setWidth(20); // 设置宽度
        $objActSheet->getColumnDimension("H")->setWidth(20); // 设置宽度 
        $objActSheet->getColumnDimension("I")->setWidth(20); // 设置宽度 
        $objActSheet->getColumnDimension("J")->setWidth(20); // 设置宽度 
        $objActSheet->getColumnDimension("K")->setWidth(20); // 设置宽度 
        $objActSheet->getColumnDimension("L")->setWidth(20); // 设置宽度 
        $objActSheet->getColumnDimension("M")->setWidth(20); // 设置宽度 
        $outputFileName = iconv('UTF-8', 'gb2312', '人员入住天数记录表.xlsx'); 
        
        //文件直接输出到浏览器  
        header('Pragma:public');  
        header('Expires:0');  
        header('Cache-Control:must-revalidate,post-check=0,pre-check=0');  
        header('Content-Type:application/force-download');  
        header('Content-Type:application/vnd.ms-excel');  
        header('Content-Type:application/octet-stream');  
        header('Content-Type:application/download');  
        header('Content-Disposition:attachment;filename=' . $outputFileName);  
        header('Content-Transfer-Encoding:binary');  
        PHPExcel_IOFactory::createWriter($objExcel, 'Excel2007')->save('php://output');
                
        //设置对齐方式      
        $objStyleA3 = $objActSheet->getStyle('A3');  
        $objAlignA3 = $objStyleA3->getAlignment();  
        $objAlignA3->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);  
        $objAlignA3->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
        
        $objExcel = null;
    }
}
